package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Pms_product_full_reduction34 {
	//插入
			@Test
			public void insert() {
				Connection conn=null;
				//准备数据库用户名，密码，url，driver
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				try {
					Class.forName(driver);//这是属于反射的内容
					//获取连接java.sq.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="insert into Pms_product_full_reduction(id,product_id,full_price,reduce_price)values(90,5,2,3)";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st=conn.createStatement();
					//执行sql命令
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"添加成功":"添加失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				
			}
			//修改
			@Test
			public void update() {
				Connection conn=null;
				//准备数据库用户名，密码，url,driver
				String  username ="root";
				String password ="root";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				String drive="com.mysql.jdbc.Driver";
				//加载驱动类到虚拟机中
				try {
					Class.forName(drive);//这是属于反射的内容
					//获取数据库连接java.sql.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="update  Pms_product_full_reduction set product_id=7 where id=1";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.statement对象st
					st=conn.createStatement();
					//执行sql命令
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"修改成功":"修改失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			@Test
			public void delete() {
				Connection conn=null;
				//准备数据库用户名，密码，url，driver
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEnconding=utf-8";
				try {
					Class.forName(driver);
					//获取数据库连接java.sql.Connection
					conn=DriverManager.getConnection(url,username,password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="delete from Pms_product_full_reduction where id =2";
				Statement st=null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement
					st=conn.createStatement();
					int row=st.executeUpdate(sql);
					System.out.println(row>0?"删除成功":"删除失败");
				} catch (SQLException e) {
					e.printStackTrace();
				}finally {
					if (st!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if (conn!=null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				
			}
			@Test
			public void selet() {
				Connection conn=null;
				//准备数据库用户名，密码，url,driver
				String username="root";
				String password="root";
				String driver="com.mysql.jdbc.Driver";
				String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				try {
					//加载驱动类到虚拟机中
					Class.forName(driver);//这属于反射的内容
					//获取数据库连接java.sql.connection
					conn=DriverManager.getConnection(url,username,password);
					
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
				String sql="select * from Pms_product_full_reduction";
				Statement st=null;
				ResultSet rs = null;
				try {
					//通过数据库连接conn获取事务java.sql.Statement对象st
					st = conn.createStatement();
					//执行sql命令
					rs = st.executeQuery(sql);
					//处理查询结果集
					while(rs.next()) {
						//System.out.println(rs.getInt(1));
						//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
						System.out.println(rs.getInt("id"));
						System.out.println(rs.getObject("product_id"));
						System.out.println(rs.getObject("full_price"));
						System.out.println(rs.getObject("reduce_price"));
						//获取username列
						//System.out.println(rs.getString("username"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if(rs != null) {
						try {
							rs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if(st != null) {
						try {
							st.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					if(conn != null) {
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
		}
			}


}
